﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WinformDevFramework.Common
{
    public class ExcelHelper
    {
        public static void ExportToExcel(DataTable dataTable, string fileName = null)
        {
            if (dataTable == null || dataTable.Rows.Count == 0)
            {
                MessageBox.Show("没有可导出的数据！", "提示",
                    MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            using (SaveFileDialog saveDialog = new SaveFileDialog())
            {
                saveDialog.FileName = fileName ?? $"导出数据_{DateTime.Now:yyyyMMddHHmm}";
                saveDialog.Filter = "Excel文件(*.xlsx)|*.xlsx|Excel 97-2003(*.xls)|*.xls";

                if (saveDialog.ShowDialog() == DialogResult.OK)
                {
                    string savedFilePath = saveDialog.FileName;

                    try
                    {
                        // 使用NPOI库导出
                        using (var fs = new FileStream(savedFilePath, FileMode.Create))
                        {
                            IWorkbook workbook = new XSSFWorkbook();
                            ISheet sheet = workbook.CreateSheet("Sheet1");

                            // 添加表头
                            IRow headerRow = sheet.CreateRow(0);
                            for (int i = 0; i < dataTable.Columns.Count; i++)
                            {
                                headerRow.CreateCell(i).SetCellValue(dataTable.Columns[i].ColumnName);
                            }

                            // 添加数据（改进数据类型处理）
                            for (int i = 0; i < dataTable.Rows.Count; i++)
                            {
                                IRow row = sheet.CreateRow(i + 1);
                                for (int j = 0; j < dataTable.Columns.Count; j++)
                                {
                                    var value = dataTable.Rows[i][j];
                                    if (value != DBNull.Value)
                                    {
                                        // 根据数据类型设置单元格值
                                        if (value is decimal)
                                            row.CreateCell(j).SetCellValue((double)(decimal)value);
                                        else
                                            row.CreateCell(j).SetCellValue(value.ToString());
                                    }
                                }
                            }

                            workbook.Write(fs);
                        }

                        if (MessageBox.Show("导出成功！是否立即打开文件？", "完成",
                            MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                        {
                            // 安全打开文件的方式
                            try
                            {
                                ProcessStartInfo psi = new ProcessStartInfo
                                {
                                    FileName = savedFilePath,
                                    UseShellExecute = true // 使用系统关联程序打开
                                };
                                Process.Start(psi);
                            }
                            catch (Exception openEx)
                            {
                                MessageBox.Show($"文件打开失败：{openEx.Message}\n文件已保存在：{savedFilePath}",
                                    "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show($"导出失败：{ex.Message}", "错误",
                            MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }
            }
        }


        public static DataTable GetExcel(string filePath)
        {
            if (!File.Exists(filePath))
                throw new FileNotFoundException("文件不存在", filePath);

            using (var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook;

                // 根据文件扩展名确定使用哪种Workbook
                if (Path.GetExtension(filePath).Equals(".xlsx", StringComparison.OrdinalIgnoreCase))
                    workbook = new XSSFWorkbook(fs); // Excel 2007+
                else
                    workbook = new HSSFWorkbook(fs); // Excel 97-2003

                if (workbook.NumberOfSheets == 0)
                    throw new Exception("Excel文件中不包含任何工作表");

                // 默认读取第一个工作表
                ISheet sheet = workbook.GetSheetAt(0);
                var dataTable = new DataTable(Path.GetFileNameWithoutExtension(filePath));

                // 处理表头
                IRow headerRow = sheet.GetRow(0);
                if (headerRow == null)
                    throw new Exception("第一行是空行，无法确定列名");

                // 添加列
                foreach (ICell headerCell in headerRow.Cells)
                {
                    string columnName = headerCell.ToString().Trim();
                    if (string.IsNullOrEmpty(columnName))
                        columnName = $"Column{headerCell.ColumnIndex + 1}";

                    // 处理重复列名
                    string uniqueName = columnName;
                    int counter = 1;
                    while (dataTable.Columns.Contains(uniqueName))
                    {
                        uniqueName = $"{columnName}({counter++})";
                    }

                    dataTable.Columns.Add(uniqueName, typeof(string));
                }

                // 处理数据行
                for (int rowIndex = 1; rowIndex <= sheet.LastRowNum; rowIndex++)
                {
                    IRow row = sheet.GetRow(rowIndex);
                    if (row == null) continue; // 跳过空行

                    DataRow dataRow = dataTable.NewRow();
                    bool hasValue = false;

                    for (int colIndex = 0; colIndex < dataTable.Columns.Count; colIndex++)
                    {
                        ICell cell = row.GetCell(colIndex);
                        if (cell != null)
                        {
                            string cellValue = GetCellValue(cell);
                            if (!string.IsNullOrEmpty(cellValue))
                            {
                                hasValue = true;
                                dataRow[colIndex] = cellValue;
                            }
                        }
                    }

                    if (hasValue) // 只添加有数据的行
                        dataTable.Rows.Add(dataRow);
                }

                return dataTable;
            }
        }

        private static string GetCellValue(ICell cell)
        {
            switch (cell.CellType)
            {
                case CellType.String:
                    return cell.StringCellValue?.Trim();
                case CellType.Numeric:
                    return DateUtil.IsCellDateFormatted(cell)
                        ? cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss")
                        : cell.NumericCellValue.ToString();
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();
                case CellType.Formula:
                    return GetCellValue(cell);
                case CellType.Error:
                    return FormulaError.ForInt(cell.ErrorCellValue).String;
                case CellType.Blank:
                default:
                    return string.Empty;
            }
        }

    }
}
